import datetime
import json

from django.db.models import Q, F, Sum, Count
from django.db.models.functions import ExtractYear, ExtractMonth, ExtractWeek
from django.utils.translation import gettext_lazy as _

from oa.models import ProductCategory, PackageUnit, CustomerLevel, ProductSalesPrice
from oa.views import AppListView, PermissionRequireTemplateView, LoginRequiredTemplateView
from tb3.utils import convert_choices_to_dict, list_export_excel
from .models import *


class StorageStaticView(LoginRequiredTemplateView):
    template_name = 'analysis/storage_static.html'

    def get_queryset(self):
        # 数据筛选
        related_calculate_id = self.request.GET.get('related_calculate_id')
        category_id = self.request.GET.get('category_id')
        warehouse = self.request.GET.get('warehouse')
        package_unit = self.request.GET.get('package_unit')
        if not related_calculate_id:
            related_calculate = StorageCalculate.objects.all().order_by('pk').last()
            if related_calculate:
                related_calculate_id = related_calculate.pk
        # 库存数据
        queryset = Storage.objects.all()
        if related_calculate_id:
            queryset = queryset.filter(
                related_calculate_id=related_calculate_id,
                related_product_info__package_group__isnull=False
            )
        else:
            queryset = queryset.none()
        if category_id:
            queryset = queryset.filter(
                related_product_info__package_group__packageitem__product_package__product_name__product_category_id=category_id)
        if warehouse:
            queryset = queryset.filter(warehouse=warehouse)
        if package_unit:
            queryset = queryset.filter(
                related_product_info__package_group__packageitem__product_package__package_unit_id=package_unit)
        return queryset

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        warehouses = list()
        data_raw = self.get_queryset().annotate(
            code=F('related_product_info__package_group__packageitem__product_package__code'),
            name=F('related_product_info__package_group__packageitem__product_package__name'),
            reference_cost=F('related_product_info__package_group__packageitem__product_package__reference_cost'),
            sales_cost=F('related_product_info__package_group__packageitem__product_package__sales_cost'),
            amount=F('related_product_info__package_group__packageitem__amount'),
        ).values(
            'code',
            'name',
            'amount',
            'warehouse',
            'reference_cost',
            'sales_cost',
        ).annotate(
            sum=Sum('current_storage'),
            count=Count('related_product_info__package_group__packageitem__product_package__name')
        ).order_by('code')
        data_m = dict()
        product_info_fields = ['left', 'packs', 'unit', 'packs_unit', 'count', 'reference_cost', 'sales_cost']
        for d in data_raw:
            if d['sum']:
                d['left'] = int(d['amount']) * int(d['sum'])
                if 'ml' in d['name']:
                    d['packs'] = round(d['left'] / 24)
                    d['packs_unit'] = '箱'
                    d['unit'] = '瓶'
                else:
                    d['packs'] = d['left']
                    d['packs_unit'] = '桶'
                    d['unit'] = '桶'
                product = '{} {}'.format(d['code'], d['name'])
                if product not in data_m:
                    data_m[product] = dict()
                if d['warehouse'] not in data_m[product]:
                    data_m[product][d['warehouse']] = dict()
                    for f in product_info_fields:
                        data_m[product][d['warehouse']][f] = d[f]
                else:
                    data_m[product][d['warehouse']]['packs'] += d['packs']
                    data_m[product][d['warehouse']]['left'] += d['left']
                    data_m[product][d['warehouse']]['count'] += d['count']
        data_list = list()
        total_p1 = 0
        total_p2 = 0
        for k, v in data_m.items():
            show = True
            rowspan = len(v)
            total_p = 0
            total_b = 0
            for k1, v1 in v.items():
                total_p += v1['packs']
                total_b += v1['left']
            for k1, v1 in v.items():
                data = dict()
                for f in product_info_fields:
                    data[f] = v1[f]
                data['name'] = k
                data['warehouse'] = k1
                data['show'] = show
                data['rowspan'] = rowspan
                data['total_p'] = total_p
                data['total_b'] = total_b
                if v1['reference_cost']:
                    data['b_p1'] = round(v1['left'] * float(v1['reference_cost']), 2)
                    data['total_b_p1'] = round(total_b * float(v1['reference_cost']), 2)
                    if show:
                        total_p1 += data['total_b_p1']
                else:
                    data['total_b_p1'] = 0
                if v1['sales_cost']:
                    data['b_p2'] = round(v1['left'] * float(v1['sales_cost']), 2)
                    data['total_b_p2'] = round(total_b * float(v1['sales_cost']), 2)
                    if show:
                        total_p2 += data['total_b_p2']
                else:
                    data['total_b_p2'] = 0
                data_list.append(data)
                show = False
                if k1 not in warehouses:
                    warehouses.append(k1)
        context['data_list'] = data_list
        context['title'] = _('产品库存统计')
        context['related_calculates'] = StorageCalculate.objects.all().order_by('-id')
        context['categories'] = ProductCategory.objects.all().order_by('-id')
        context['warehouses'] = warehouses
        context['package_units'] = PackageUnit.objects.filter(is_product=True)
        context['total_p1'] = round(total_p1, 2)
        context['total_p2'] = round(total_p2, 2)
        return context

    @staticmethod
    def export_storage_list(context):
        headers = [
            '产品', '仓库', '库存（瓶或桶）', '库存（箱或桶）', ' 生产成本（元）', '出厂成本（元）',
            '库存生产成本（元）', '库存出厂成本（元）'
        ]
        columns = [
            'name', 'warehouse', 'left', 'packs', 'reference_cost', 'sales_cost', 'b_p1', 'b_p2'
        ]
        rows = context['data_list']
        return list_export_excel(
            data_list=rows,
            headers=headers,
            columns=columns,
            filename='media/storages_{}.xls'.format(datetime.datetime.now().strftime('%Y%m%d%H%M%S'))
        )

    def get(self, request, *args, **kwargs):
        context = self.get_context_data(**kwargs)
        if request.GET.get('excel') == "1":
            return self.export_storage_list(context)
        return self.render_to_response(context)


class ProductSaleStaticView(AppListView):
    template_name = 'analysis/product_sales.html'
    model = SalesRecord

    def get_queryset(self):
        queryset = super().get_queryset().exclude(related_customer__need_statistic=False)
        c = self.request.GET.get('channel_id')
        customer_level_id = self.request.GET.get('customer_level_id')
        date_start = self.request.GET.get('date_start')
        date_end = self.request.GET.get('date_end')
        get_all = self.request.GET.get('get_all', False)
        salesman = self.request.GET.get('salesman')
        # 客户
        if customer_level_id:
            queryset = queryset.filter(related_customer__customer_level_id=customer_level_id)
        # 开始日期
        if date_start:
            date_start = date_value(date_start)
            if date_start:
                queryset = queryset.filter(
                    sales_date__gte=date_start
                ).order_by('sales_date').distinct()
        # 截止日期
        if date_end:
            date_end = date_value(date_end)
            if date_end:
                queryset = queryset.filter(
                    sales_date__lte=date_end
                ).order_by('sales_date').distinct()
        # 销售渠道
        if c:
            c = int(c)
            queryset = queryset.filter(sales_channel=c)
        # 销售人员
        if salesman:
            queryset = queryset.filter(salesman__icontains=salesman)
        # 获取所有数据
        if not (get_all or date_start or date_end):
            date_start = '{0}-01'.format((datetime.datetime.now() - datetime.timedelta(days=90)).strftime('%Y-%m'))
            queryset = queryset.filter(sales_date__gte=date_start)
        return queryset

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        data = self.sales_static()
        for k, v in data.items():
            context[k] = v
        context['total_count'] = self.get_queryset().count()
        context['title'] = _('销售数据统计')
        context['channels'] = [{'pk': 1, 'name': '上海'}, {'pk': 2, 'name': '杭州'}]
        context['pack_types'] = [{'pk': 1, 'name': '箱'}, {'pk': 2, 'name': '瓶'}]
        context['is_drafts'] = [{'pk': 1, 'name': '是扎啤'}, {'pk': 2, 'name': '是瓶啤'}]
        context['gbs'] = [{'display': '按周', 'name': 'week'}, {'display': '按月', 'name': 'month'}]
        context['customer_levels'] = CustomerLevel.objects.all().order_by('level_code')
        return context

    def sales_static(self):
        # 按月还是按周
        is_draft = self.request.GET.get('is_draft')
        gb = self.request.GET.get('gb', 'month')
        pt = self.request.GET.get('pt', '1')
        if gb not in ['week', 'month']:
            gb = 'month'
        queryset = self.get_queryset().filter(related_product_info__package_group__isnull=False)
        # 搜索
        q = self.request.GET.get('q')
        if q:
            queryset = queryset.filter(
                Q(related_product_info__package_group__packageitem__product_package__name__icontains=q) | Q(
                    related_product_info__package_group__packageitem__product_package__code__icontains=q)
            ).distinct()
        if is_draft:
            if is_draft == '1':
                queryset = queryset.filter(
                    related_product_info__package_group__packageitem__product_package__is_draft=True
                )
            else:
                queryset = queryset.filter(
                    related_product_info__package_group__packageitem__product_package__is_draft=False
                )
        sales_raw = queryset.annotate(
            product=F('related_product_info__package_group__packageitem__product_package__name'),
            pack_amount=F('related_product_info__package_group__packageitem__amount'),
        ).values(
            'sales_date', 'product', 'pack_amount', 'amount'
        ).annotate(
            year=ExtractYear('sales_date'),
            month=ExtractMonth('sales_date'),
            week=ExtractWeek('sales_date')
        ).values(
            'year', gb, 'product', 'pack_amount'
        ).annotate(
            sales=Sum('amount')
        ).order_by('year', gb)
        sales = dict()
        products = dict()
        year_gbs = list()
        series_data = list()
        for s in sales_raw:
            in_count = False
            if q:
                if q in s['product']:
                    in_count = True
            else:
                in_count = True
            if in_count:
                if s['product'] not in products:
                    products[s['product']] = list()
                if s['year'] not in sales:
                    sales[s['year']] = dict()
                if s[gb] not in sales[s['year']]:
                    sales[s['year']][s[gb]] = dict()
                    year_gb = '{}-{}'.format(s['year'], s[gb])
                    year_gbs.append(year_gb)
                if s['product'] not in sales[s['year']][s[gb]]:
                    if 'ml' in s['product']:
                        if pt == "1":
                            sales[s['year']][s[gb]][s['product']] = {'amount': 0, 'unit': '箱'}
                        else:
                            sales[s['year']][s[gb]][s['product']] = {'amount': 0, 'unit': '瓶'}
                    else:
                        sales[s['year']][s[gb]][s['product']] = {'amount': 0, 'unit': '桶'}
                if 'ml' in s['product']:
                    if pt == "1":
                        sales[s['year']][s[gb]][s['product']]['amount'] += float(s['pack_amount']) * float(s['sales']) / 24
                    else:
                        sales[s['year']][s[gb]][s['product']]['amount'] += float(s['pack_amount']) * float(s['sales'])
                else:
                    sales[s['year']][s[gb]][s['product']]['amount'] += float(s['pack_amount']) * float(s['sales'])
        for k, v in products.items():
            products[k] = [0]*len(year_gbs)
        for year, v1 in sales.items():
            for week, product_info in v1.items():
                for product, amount in product_info.items():
                    year_gb = '{}-{}'.format(year, week)
                    products[product][year_gbs.index(year_gb)] = round(amount.get('amount', 0), 1)
        for p_name, data in products.items():
            series_data.append({'name': p_name, 'data': data})
        data = {'sales': sales, 'series_data': series_data, 'year_gbs': year_gbs}
        return data


class StaffSalesAnalysisView(PermissionRequireTemplateView):
    template_name = 'analysis/staff_sales_analysis.html'
    permission_required = ('oa.view_moneyinout', )
    permission_denied_message = '权限不够，找老板'

    def get_queryset(self):
        queryset = SalesRecord.objects.exclude(
            related_customer__need_statistic=False
        )
        date_start = self.request.GET.get('date_start')
        if date_start:
            date_start = date_value(date_start)
            if date_start:
                queryset = queryset.filter(
                    sales_date__gte=date_start
                ).order_by('sales_date').distinct()
        date_end = self.request.GET.get('date_end')
        if date_end:
            date_end = date_value(date_end)
            if date_end:
                queryset = queryset.filter(
                    sales_date__lte='{}'.format(date_end)
                ).order_by('sales_date').distinct()
        return queryset

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        queryset = self.get_queryset().filter(salesman__isnull=False)
        sales_data = queryset.values(
            'salesman',
        ).annotate(
            total_income=Sum('income')
        ).values(
            'salesman', 'total_income'
        ).order_by('-total_income')
        context['data'] = sales_data
        return context


class SalesStaticView(AppListView):
    template_name = 'analysis/sales_static.html'
    model = SalesRecord

    def get_queryset(self):
        queryset = super().get_queryset().exclude(
            related_customer__need_statistic=False
        )
        date_start = self.request.GET.get('date_start')
        date_end = self.request.GET.get('date_end')
        channel_id = self.request.GET.get('channel_id')
        if date_start:
            date_start = date_value(date_start)
            if date_start:
                queryset = queryset.filter(
                    sales_date__gte=date_start
                ).order_by('sales_date')
        # 截止日期
        if date_end:
            date_end = date_value(date_end)
            if date_end:
                queryset = queryset.filter(
                    sales_date__lte='{}'.format(date_end)
                ).order_by('sales_date')
        # 销售渠道
        if channel_id:
            channel_id = int(channel_id)
            queryset = queryset.filter(sales_channel=channel_id)
        return queryset

    def get_context_data(self, **kwargs):
        context = dict()
        sales_raw = self.get_queryset().annotate(
            level=F('related_customer__customer_level__level_name')
        ).values(
            'level',
        ).annotate(
            year=ExtractYear('sales_date'),
            month=ExtractMonth('sales_date')
        ).values(
            'year', 'month', 'level',
        ).annotate(
            income_sum=Sum('income')
        ).values('year', 'month', 'level', 'income_sum').order_by('-year', '-month')
        sales_data = dict()
        year_gbs = list()
        levels = list()
        for s in sales_raw:
            level = s['level']
            if not level:
                level = 'unknown'
            year_gb = '{}-{}'.format(s['year'], s['month'])
            if year_gb not in year_gbs:
                year_gbs.append(year_gb)
            if level not in levels:
                levels.append(level)
            if s['year'] not in sales_data:
                sales_data[s['year']] = dict()
            if s['month'] not in sales_data[s['year']]:
                sales_data[s['year']][s['month']] = dict()
            if level not in sales_data[s['year']][s['month']]:
                sales_data[s['year']][s['month']][level] = float(s['income_sum'])
        # levels = sorted(levels)
        series_dict = dict()
        for s in sales_raw:
            level = s['level']
            if not level:
                level = 'unknown'
            year_gb = '{}-{}'.format(s['year'], s['month'])
            if level not in series_dict:
                series_dict[level] = {'name': level, 'data': [0]*len(year_gbs)}
            year_index = int(year_gbs.index(year_gb))
            series_dict[level]['data'][year_index] = float(s['income_sum'])
        series_data = list()
        for k, v in series_dict.items():
            series_data.append(v)
        context['levels'] = levels
        context['sales_data'] = sales_data
        context['series_data'] = series_data
        context['year_gbs'] = year_gbs
        context['channels'] = [{'pk': 1, 'name': '上海'}, {'pk': 2, 'name': '杭州'}]
        return context


class DepartmentSalesStatisticView(AppListView):
    template_name = 'analysis/department_sales_statistic.html'
    model = SalesRecord

    def get_queryset(self):
        queryset = SalesRecord.objects.exclude(
            related_customer__need_statistic=False
        ).exclude(
            related_product_info__need_statistic=False
        )
        date_start = self.request.GET.get('date_start')
        date_end = self.request.GET.get('date_end')
        channel_id = self.request.GET.get('channel_id')
        # 开始日期
        if date_start:
            date_start = date_value(date_start)
            if date_start:
                queryset = queryset.filter(
                    sales_date__gte=date_start
                ).order_by('sales_date')
        # 截止日期
        if date_end:
            date_end = date_value(date_end)
            if date_end:
                queryset = queryset.filter(
                    sales_date__lte='{}'.format(date_end)
                ).order_by('sales_date')
        # 销售渠道
        if channel_id:
            channel_id = int(channel_id)
            queryset = queryset.filter(sales_channel=channel_id)
        return queryset

    def get_context_data(self, **kwargs):
        context = super(DepartmentSalesStatisticView, self).get_context_data(**kwargs)
        data = dict()
        profit_raw = self.get_queryset().annotate(
            year=ExtractYear('sales_date'),
            month=ExtractMonth('sales_date'),
        ).values(
            'sales_channel',
            'year',
            'month',
        ).annotate(
            total_income=Sum('income'),
            product_costs=Sum('sales_cost'),
        ).order_by('-year', '-month')
        categories = list()
        series_dict = dict()
        for d in profit_raw:
            sales_channel = convert_choices_to_dict(sales_channel_choices).get(d['sales_channel'])
            c = '{}-{}'.format(d['year'], d['month'])
            if d['year'] not in data:
                data[d['year']] = dict()
            if d['month'] not in data[d['year']]:
                data[d['year']][d['month']] = dict()
            if c not in categories:
                categories.append(c)
            if sales_channel not in data[d['year']][d['month']]:
                data[d['year']][d['month']][sales_channel] = {
                    'total_income': 0,
                    'product_costs': 0,
                    'total_profits': 0
                }
            if d['total_income']:
                data[d['year']][d['month']][sales_channel]['total_income'] += float(d['total_income'])
            if d['product_costs']:
                data[d['year']][d['month']][sales_channel]['product_costs'] += float(d['product_costs'])
            data[d['year']][d['month']][sales_channel]['total_profits'] = \
                data[d['year']][d['month']][sales_channel]['total_income'] - \
                data[d['year']][d['month']][sales_channel]['product_costs']
            data[d['year']][d['month']][sales_channel]['profits_per'] = \
                100 * data[d['year']][d['month']][sales_channel]['total_profits'] / \
                data[d['year']][d['month']][sales_channel]['total_income']
        categories = sorted(categories)
        for year, data_m in data.items():
            for month, data_r in data_m.items():
                y_m = '{}-{}'.format(year, month)
                for dept, data_f in data_r.items():
                    series_key = '{}-{}'.format(dept, '收入')
                    if series_key not in series_dict:
                        series_dict[series_key] = [0]*len(categories)
                        series_dict['{}-{}'.format(dept, '成本')] = [0]*len(categories)
                        series_dict['{}-{}'.format(dept, '利润')] = [0]*len(categories)
                    series_dict[series_key][categories.index(y_m)] = data_f['total_income']
                    series_dict['{}-{}'.format(dept, '成本')][categories.index(y_m)] = data_f['product_costs']
                    series_dict['{}-{}'.format(dept, '利润')][categories.index(y_m)] = data_f['total_profits']
        series = list()
        for k, v in series_dict.items():
            series.append({'name': k, 'data': v})
        context['data'] = data
        context['categories'] = categories
        context['series'] = series
        context['channels'] = [{'pk': 1, 'name': '上海'}, {'pk': 2, 'name': '杭州'}]
        return context
